[dbt] 作成したデータモデルに対してテストを実行する
大阪オフィスの玉井です。
dbtはSQLだけで柔軟なデータ変換を作ることが出来ますが、作成したデータに対してテストを実行することができます。
「データをテストする」ということ
ソフトウェアエンジニアリングではテストするのが普通
何らかのアプリケーションを開発されたことのある方ならわかると思いますが、開発したものに対しては、必ずテストを行うと思います。特に、昨今のアプリケーション開発では、テストコードなるものを記述することも珍しくありませんよね(私は「人力+Excelにスクショ貼り付け」の時代しか知らない人間です)。
コードにできるということは、バージョン管理ができるということです。そして、CI/CDの手法がとれるということです。CIというのは、継続的インテグレーションの略で、ざっくり言うと、共有リポジトリにコードをマージした際、自動でビルドとテストをやってくれるというものです。
この、アプリケーション開発では当たり前の概念・手法を取り入れた例としてInfrastructure as Codeがあると思います。インフラ基盤の構成等をコードで運用管理する手法ですね。これにより、インフラ自体も(間接的に?)バージョン管理することができます。私は詳しくありませんが、「インフラCI/CD」という言葉もあるようです。
dbtは生成したデータに対するテストをコードで書ける
dbtは、作成したデータ変換(データモデル)に対して、コードでテストを定義できるようになっています。コードでテストを書けるということは、テスト自体のバージョン管理もできるし、CIも実現できます。
dbtは「アナリティクスエンジニアリング」という考え方を提唱しており、アプリケーション開発の手法をデータ変換作業に用いています。このテスト機能は、まさにそれの一環ですね。
もう少し砕いて言うと、「dbtで作ったテーブルやビューのデータが間違ってないかどうか(要件に沿っているかどうか)テストできる(コードが書ける)」感じです。アプリケーション開発におけるテストコードより、ずっと簡単にできます。
dbtにおけるデータのテスト
dbtでは2種類のテストを定義・実行することができます。
- Schema test
- YAMLファイルで定義する
- 「このカラムにNULLがないこと」等の簡易なテストを実行できる
- Data test
- SQL(SELECT文)で定義するテスト
- 結果が0件になるようなテストクエリを書いて実行する
やってみた
環境
- macOS Catalina 10.15.7
- dbt CLI 0.18.1
- Google BigQuery
Schema testの設定と実行
テストを実行するモデル
下記のモデルに対してテストを実行します。
こちらは、よくある注文テーブルだと思ってください。
select id as order_id, user_id as customer_id, order_date, status from orders
こちらは、よくある顧客テーブルです。
select id as customer_id, first_name, last_name from customers
生成するテーブルには要件があると思います。例えば「顧客IDは、値はそれぞれユニークである(ダブってはいけない)」「オーダーIDにNULLが入っててはいけない」など。当たり前っちゃ当たり前の要件ですが、データをいじくり回しているうちに、こういった要件を破ってしまうことは往々にしてあります。ですので、dbtでそういった条件を予めテストできるようにしましょう。
Schrma testを記述する
定義方法ですが、データモデルのファイルと同じ階層にYAMLファイルを作成して、そこにテストを定義する形となります。で、そのYAMLファイルに定義する内容ですが、今回は以下の通りです。
version: 2 models: # テストしたいモデルを指定 - name: stg_customers # テストしたいカラムを指定 columns: - name: customer_id # 実行するテストを指定 tests: - unique - not_null - name: stg_orders columns: - name: order_id tests: - unique - not_null - name: status tests: - accepted_values: values: - completed - shipped - returned - placed - return_pending - name: customer_id tests: - relationships: to: ref('stg_customers') field: customer_id
めちゃくちゃ単純ですね。データモデルの名前→カラムの名前→実行したいテストの種類、という順番に書いていくだけです。
unique
とnot_null
例えば、stg_customers
のcustomer_id
に対しては、unique
とnot_null
が定義されています。
これは、customer_id
の値が一意かつNULLが入ってないかどうかをテストするようになっています。値がダブっていたり、NULLがあったりすると、テストは通らないということです。
accepted_values
stg_orders
のstatus
に対しては、accepted_values
というテストが定義されています。
これは、その名の通り、「指定した値以外が入っていないかどうかテストする」というものです。values
というパラメータで、このカラムの値として許可するものをリストアップします。ここに書いてある値以外が入ってくるとエラーとなります。
relationships
stg_orders
のcustomer_id
には、relationships
というテストが定義されています。
これはシンプルにいうと参照整合性をテストするものです。この場合でいうと、stg_orders
のcustomer_id
は、必ずstg_customers
のcustomer_id
に存在することをテストします。顧客マスタに存在しない顧客IDの注文が入っていたらおかしい…ということですね。
昨今のDWHは、その性質上、参照整合性の制約を機能として持たなかったりするので、dbtで参照整合性をテストできるのは、非常に便利だと思います。
実行
記述したSchrma testは、dbt test
で実行できます。
$ dbt test Running with dbt=0.18.1 Found 5 models, 7 tests, 0 snapshots, 0 analyses, 155 macros, 0 operations, 0 seed files, 0 sources 16:04:35 | Concurrency: 4 threads (target='learn') 16:04:35 | 16:04:35 | 1 of 6 START test accepted_values_stg_orders_status__completed__shipped__returned__placed__return_pending [RUN] 16:04:35 | 2 of 6 START test not_null_stg_customers_customer_id................. [RUN] 16:04:35 | 3 of 6 START test not_null_stg_orders_order_id....................... [RUN] 16:04:35 | 4 of 6 START test relationships_stg_orders_customer_id__customer_id__ref_stg_customers_ [RUN] 16:04:38 | 4 of 6 PASS relationships_stg_orders_customer_id__customer_id__ref_stg_customers_ [PASS in 3.03s] 16:04:38 | 2 of 6 PASS not_null_stg_customers_customer_id....................... [PASS in 3.03s] 16:04:38 | 5 of 6 START test unique_stg_customers_customer_id................... [RUN] 16:04:38 | 6 of 6 START test unique_stg_orders_order_id......................... [RUN] 16:04:38 | 1 of 6 PASS accepted_values_stg_orders_status__completed__shipped__returned__placed__return_pending [PASS in 3.05s] 16:04:39 | 3 of 6 PASS not_null_stg_orders_order_id............................. [PASS in 3.45s] 16:04:41 | 5 of 6 PASS unique_stg_customers_customer_id......................... [PASS in 2.96s] 16:04:42 | 6 of 6 PASS unique_stg_orders_order_id............................... [PASS in 3.50s] 16:04:42 | 16:04:42 | Finished running 6 tests in 8.97s. Completed successfully Done. PASS=6 WARN=0 ERROR=0 SKIP=0 TOTAL=6
これらのテストが実際にどう行われているかなのですが、先程作成したYAMLファイルの記述に沿って、テスト用のSQLクエリがコンパイルされて実行されます。要するに、テストもSQLで行われるということです(dbtではなくDWH側で行われる処理となる)。
例えば、relationships
のテストは、下記のように行われていました(コンパイル後のクエリは、プロジェクトの/target/compiled/
下に保存されます。DWH側のログを見るのもアリです)。
select count(*) as validation_errors from ( select customer_id as id from `スキーマ名`.`stg_orders` ) as child left join ( select customer_id as id from `スキーマ名`.`stg_customers` ) as parent on parent.id = child.id where child.id is not null and parent.id is null
このテストクエリの結果が0件だったら、テスト通過となります。
Data testの設定と実行
Data testは、Schema testとは異なり、実際にSELECT文を書いて定義します。テスト用のクエリなので、書き方としては「結果が0件だったらOK」という形になります。個人的な感想なのですが、Schema testでは実現できない、ユーザー独自のテストがある場合は、こちらのData testで定義する感じです。アプリケーション開発におけるテストコードに近いのは、このData testでしょうか。
今回は、公式ドキュメントのサンプルのData test用のクエリを使います。
select order_id, sum(amount) as total_amount from {{ ref('stg_payments') }} group by 1 having not(total_amount >= 0)
stg_payments
という、支払いデータが入っているテーブルがあるとします。
このData testは、注文ID毎に集計した合計金額がマイナスにならないかどうかをテストします(ファイル名はassert_positive_value_for_total_amount.sql
とします)。システム上、払い戻し等の処理があるため、減額処理の計算の誤り等で、合計金額がマイナスになっていないかどうかチェックする必要があるということですね。
Data testを書いたら、SQLファイルとして、プロジェクトの/tests
配下に配置します。基本、1テスト1ファイルにします(1ファイルにSELECT文が1つ)。
実行
これもdbt test
で実行できます。ちなみに、dbt test --data
にすると、Data testだけ実行することができます。
$ dbt test --data Running with dbt=0.18.1 Found 5 models, 7 tests, 0 snapshots, 0 analyses, 155 macros, 0 operations, 0 seed files, 0 sources 16:19:58 | Concurrency: 4 threads (target='learn') 16:19:58 | 16:19:58 | 1 of 1 START test assert_positive_value_for_total_amount............. [RUN] 16:20:02 | 1 of 1 PASS assert_positive_value_for_total_amount................... [PASS in 4.04s] 16:20:02 | 16:20:02 | Finished running 1 test in 5.62s. Completed successfully Done. PASS=1 WARN=0 ERROR=0 SKIP=0 TOTAL=1
Data testは元々テスト用のクエリをこちらで記述するものなので、コンパイルされるクエリもさほど違いはありません。せっかくなので、実際に実行したBQ側のスクショを貼り付けておきます。
自動テスト
今まで実行してきたテストは、手動でdbtコマンドを叩くものでした。しかし、dbtを使えば、データ変換の自動テストを行うことができます。
今回はdbt CLIを使っていますが、例えばdbt Cloudであれば、クラウド上でdbtを本番運用するためのジョブを設定することができます。
このジョブにテストコマンドを仕込んでおけば、スケジュール実行で定期的にテストを自動実行させることができます。リポジトリへのプルリクエストがあった時に実行するように設定することもできます。これにより、データ変換のCIを実現することができます。新しいデータ変換のロジックやデータモデル等が追加される時、自動でテストが実行され、他のデータモデルに影響が出ていないか(デグレってないか)チェックすることができます。
詳細はこちらをどうぞ。
おわりに
アプリケーション開発におけるテストコードもそうですが、こういうテストを考えるためには、そもそも要件や仕様をしっかり理解する必要があるため、データ変換においても、こうやってテストを意識するのは、非常に有用だと思いました。